<?php include '../../authorization_check.php' ?>
<?php include '../../checkSecretaryOnly.php' ?>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-9">
<title>Exam Scheduling</title>
<SCRIPT TYPE="text/javascript" src= "../../commonutils/util/util.js"> </SCRIPT>
</head>
<style type="text/css">
th {text-align: right}
</style>
<body>

<?php include 'top.html' ?>

<?php include 'notifier.php' ?>
  
<FORM NAME ="form1" METHOD ="POST" ACTION = "ScheduleExam.php">
<?php
require_once '../../commonutils/db/DBConnectionManager.php';
$numerrors = 0;
if (isset($_POST['btnSubmit']))
{
	$sectionID = $_POST['sectionID'];
	$examType = $_POST['examType'];
	$examDate = $_POST['eyyyy']."-".$_POST['emm']."-".$_POST['edd'];
	$startTime = $_POST['sho'].":".$_POST['smi'];
	$endTime = $_POST['eho'].":".$_POST['emi'];
	$room = strtoupper($_POST['room']);
	
	
	
	if($endTime <= $startTime){
		$numerrors = $numerrors + 1;
		echo "<p><font color=red> <b>* Exam ending time must be later than starting time. </b> </font></p>";		
	}
	
	$sqlquery = " select count(*) as cnt from section s, exam e ".
				" where s.semesterID = ".$semesterID." and e.semesterID = ".$semesterID.
				" and s.sectionID !=  ".$sectionID." and e.sectionID = s.sectionID ".
				" and e.examDate = '".$examDate."'".
      			" and ((e.startTime >= '".$startTime."' and e.startTime <= '".$endTime."') or ".
      			"      (e.endTime >= '".$startTime."' and e.endTime <= '".$endTime."'))";
	$result = DBConnectionManager::query($sqlquery);
	$row = mysql_fetch_assoc($result);
	$cnt = $row['cnt'];
	if($cnt > 0){
		$numerrors = $numerrors + 1;
		echo "<hr color=red>";
		echo "<font color=red><b> <p>* There is a schedule conflict for ".$cnt." students. </p> ";

		//echo $sqlquery."<br>";
		echo "<p>Other exams that cause conflict are:</p></b></font>";
		
		$sqlquery = " select c.courseCode, c.courseName, s.sectionNumber, e.examDate, e.startTime, e.endTime,
							 count(*) as cnt from section s, exam e, course c ".
				" where s.semesterID = ".$semesterID." and e.semesterID = ".$semesterID.
				" and s.sectionID !=  ".$sectionID." and e.sectionID = s.sectionID and c.courseCode = s.courseCode ".
				" and e.examDate = '".$examDate."'".
      			" and ((e.startTime >= '".$startTime."' and e.startTime <= '".$endTime."') or ".
      			"      (e.endTime >= '".$startTime."' and e.endTime <= '".$endTime."'))".
				" group by c.courseCode, c.courseName, s.sectionNumber, e.examDate, e.startTime, e.endTime ";		
		$result = DBConnectionManager::query($sqlquery);
		
		echo "<p>";
		echo "<table border=1>";
		echo "<tr>";
		echo "<th style=text-align:center>COURSE CODE </th>";
		echo "<th style=text-align:center>COURSE NAME </th>";
		echo "<th style=text-align:center>SECTION </th>";
		echo "<th style=text-align:center>EXAM DATE </th>";
		echo "<th style=text-align:center>START TIME </th>";
		echo "<th style=text-align:center>END TIME </th>";
		echo "<th style=text-align:center>NUMBER OF STUDENTS</th>";
		echo "</tr>";
		
		while($row = mysql_fetch_assoc($result)){
			echo "<tr>";
			echo "<td>".$row['courseCode']."</td>";
			echo "<td>".$row['courseName']."</td>";
			echo "<td>".$row['sectionNumber']."</td>";
			echo "<td>".$row['examDate']."</td>";
			echo "<td>".$row['startTime']."</td>";
			echo "<td>".$row['endTime']."</td>";
			echo "<td>".$row['cnt']."</td>";		
			echo "</tr>";
		}
		echo "</table>";
		echo "</p>";
		echo "<hr color=red>";
	}
	  
	
	if($numerrors == 0){
		$sqlquery = "insert into exam(examType, examDate, startTime, endTime, roomCode, sectionID, semesterID) values"
					."(".$examType.", '".$examDate."', '".$startTime."', '".$endTime."', '".$room."', ".$sectionID.", ".$semesterID.")" ;
		// echo $sqlquery; 
		$result = DBConnectionManager::query($sqlquery);
		//$row = mysql_fetch_assoc($result);
	    echo "<p><font color=blue> <b>You have successfully added a new exam. </b> </font></p>";
	    echo "<p><a href=ViewExams.php> View secheduled exams. </a></p>";
	}

}

?>
<p>
<table border=1 cellpadding=3>
<tr>
<th></th>
<th style="text-align:center">COURSE CODE </th>
<th style="text-align:center">COURSE NAME </th>
<th style="text-align:center">SECTION </th>
<th style="text-align:center">INSTRUCTOR </th>
</tr>
<?php
require_once '../../commonutils/db/DBConnectionManager.php';

	$sqlquery = " select s.sectionID, c.courseCode, c.courseName, s.sectionNumber, i.firstname, i.lastname ".
				" from course c, section s, instructor i ".
				" where s.courseCode = c.courseCode and s.instructorID = i.instructorID ".
				" and s.semesterID = ".$semesterID." and c.programCode = ".$programCode.
				" order by courseCode, sectionNumber ";
	
	// echo $sqlquery."<br>"; 


	$result = DBConnectionManager::query($sqlquery);
	while($row = mysql_fetch_assoc($result)){
		echo "<tr>";
		echo "<td><input type=radio name=sectionID ";
		if(isset($_POST['btnSubmit']) && $numerrors > 0 && $row['sectionID'] == $_POST['sectionID'] ) 
		   echo " checked ";
		echo " value= ".$row['sectionID']."></td>";
		echo "<td>".$row['courseCode']."</td>";
		echo "<td>".$row['courseName']."</td>";
		echo "<td>".$row['sectionNumber']."</td>";
		echo "<td>".$row['firstname']." ".$row['lastname']."</td>";
		echo "</tr>";		
	}
	
?>
</table>
</p>

<p>
<table>
<tr>
<td>Exam Type: </td>
<td><select name="examType"  />
	<option value="1" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['examType'] == 1) echo " selected "; ?> >Midterm</option>
	<option value="2" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['examType'] == 2) echo " selected "; ?>>Final</option>
</tr>

<tr>
<td>Exam date(DD/MM/YYYY):</td>
<td> 
		<select name="edd" > 
		    <?php 
			for($i = 1; $i <= 31; $i = $i + 1){
			   if( $i < 10 )
			   	 $str = "0".$i;
			   else	 
			     $str = $i;
			   echo " <option value=".$str;
			   if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['edd'] == $str)
			      echo " selected "; 
			   echo " > ".$i."</option>";  
			     
			}
			?>
					
		</select>
		 /
		<select name="emm" > 
			<option value="01" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "01") echo " selected "; ?> >January</option>
			<option value="02" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "02") echo " selected "; ?> >February</option>
			<option value="03" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "03") echo " selected "; ?> >March</option>
			<option value="04" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "04") echo " selected "; ?> >April</option>
			<option value="05" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "05") echo " selected "; ?> >May</option>
			<option value="06" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "06") echo " selected "; ?> >June</option>
			<option value="07" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "07") echo " selected "; ?> >July</option>
			<option value="08" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "08") echo " selected "; ?> >August</option>
			<option value="09" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "09") echo " selected "; ?> >September</option>
			<option value="10" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "10") echo " selected "; ?> >October</option>
			<option value="11" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "11") echo " selected "; ?> >November</option>
			<option value="12" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emm'] == "12") echo " selected "; ?> >December</option>
		</select>
		/
		<select name="eyyyy" > 
			<option value="2008" <?php if($year == 2008) echo "selected"; ?> >2008</option>
			<option value="2009" <?php if($year == 2009) echo "selected"; ?> >2009</option>
			<option value="2010" <?php if($year == 2010) echo "selected"; ?> >2010</option>
			<option value="2011" <?php if($year == 2011) echo "selected"; ?> >2011</option>
			<option value="2012" <?php if($year == 2012) echo "selected"; ?> >2012</option>
			<option value="2013" <?php if($year == 2013) echo "selected"; ?> >2013</option>
			<option value="2014" <?php if($year == 2014) echo "selected"; ?> >2014</option>
			<option value="2015" <?php if($year == 2015) echo "selected"; ?> >2015</option>
		</select>
</td>
</tr>

<tr>
<td>Start Time(HH:MM):</td>
<td> 
<select name="sho" > 
	<option value="08" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "08") echo " selected "; ?>>08</option>
	<option value="09" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "09") echo " selected "; ?>>09</option>
	<option value="10" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "10") echo " selected "; ?>>10</option>
	<option value="11" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "11") echo " selected "; ?>>11</option>
	<option value="12" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "12") echo " selected "; ?>>12</option>
	<option value="13" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "13") echo " selected "; ?>>13</option>
	<option value="14" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "14") echo " selected "; ?>>14</option>
	<option value="15" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "15") echo " selected "; ?>>15</option>
	<option value="16" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "16") echo " selected "; ?>>16</option>
	<option value="17" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "17") echo " selected "; ?>>17</option>
	<option value="18" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "18") echo " selected "; ?>>18</option>
	<option value="19" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['sho'] == "19") echo " selected "; ?>>19</option>
</select>
 :
<select name="smi" > 
	<option value="00" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['smi'] == "00") echo " selected "; ?>>00</option>
	<option value="10" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['smi'] == "10") echo " selected "; ?>>10</option>
	<option value="20" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['smi'] == "20") echo " selected "; ?>>20</option>
	<option value="30" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['smi'] == "30") echo " selected "; ?>>30</option>
	<option value="40" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['smi'] == "40") echo " selected "; ?>>40</option>
	<option value="50" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['smi'] == "50") echo " selected "; ?>>50</option>
</select>
</td>
</tr>

<tr>
<td>End Time(HH:MM):</td>
<td> 
<select name="eho" > 

	<option value="09" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "08") echo " selected "; ?>>09</option>
	<option value="10" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "10") echo " selected "; ?>>10</option>
	<option value="11" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "11") echo " selected "; ?>>11</option>
	<option value="12" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "12") echo " selected "; ?>>12</option>
	<option value="13" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "13") echo " selected "; ?>>13</option>
	<option value="14" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "14") echo " selected "; ?>>14</option>
	<option value="15" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "15") echo " selected "; ?>>15</option>
	<option value="16" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "16") echo " selected "; ?>>16</option>
	<option value="17" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "17") echo " selected "; ?>>17</option>
	<option value="18" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "18") echo " selected "; ?>>18</option>
	<option value="19" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['eho'] == "19") echo " selected "; ?>>19</option>
</select>
 :
<select name="emi" > 
	<option value="00" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emi'] == "00") echo " selected "; ?>>00</option>
	<option value="10" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emi'] == "10") echo " selected "; ?>>10</option>
	<option value="20" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emi'] == "20") echo " selected "; ?>>20</option>
	<option value="30" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emi'] == "30") echo " selected "; ?>>30</option>
	<option value="40" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emi'] == "40") echo " selected "; ?>>40</option>
	<option value="50" <?php if(isset($_POST['btnSubmit']) && $numerrors > 0 && $_POST['emi'] == "50") echo " selected "; ?>>50</option>
</select>
</td>

<tr>
<td>Room: </td>
<td> 
<input type="text" name="room" 
<?php if(isset($_POST['btnSubmit']) && $numerrors > 0 ) echo " value=".$_POST['room']." "; ?>
 size="10" maxlength="10" style="text-transform: uppercase"/> 
</td>
</tr>
</table>
</p>

<p>
<input type="submit" value=" Save the Exam " name="btnSubmit" onClick="return radioControl(sectionID)"> &nbsp;
<input type="reset" value=" Reset Fields " name="btnReset">
</p>

<p>

</p>  
</FORM>

<?php include 'bottom.php' ?>

</body>


</html>